package com.software.util

object SparkSql {
  val selectByDay1 =
    """
      |select date,type,hour,`北京` AS BeiJing
      |from beijing_day1
      |where type IN ('PM2.5','PM10','SO2','NO2','O3','CO')
      |AND date BETWEEN '20220101' AND '20220107'""".stripMargin

  val selectByDay2 =
    """
      |SELECT date,type,ROUND(AVG(CAST(BeiJing AS FLOAT)), 2) AS average
      |FROM beijing_day2
      |GROUP BY date,type;""".stripMargin

  val selectByHour =
    """
      |select type,hour,`北京`
      |from beijing_hour
      |where type IN ('PM2.5','PM10','SO2','NO2','O3','CO')
      |AND date = '20220101'""".stripMargin

  val selectByMonth1 =
    """
      |select left(date, 6) AS date ,type,hour,`北京` AS BeiJing
      |from beijing_month1
      |where type IN ('PM2.5','PM10','SO2','NO2','O3','CO')
      |AND date BETWEEN '20220101' AND '20221231'""".stripMargin

  val selectByMonth2 =
    """
      |SELECT date,type,ROUND(AVG(CAST(BeiJing AS FLOAT)), 2) AS average
      |FROM beijing_month2
      |GROUP BY date,type;""".stripMargin

  val selectByPoll =
    """
      |SELECT type,ROUND(AVG(CAST(`北京` AS FLOAT)), 2) AS average
      |FROM beijing_poll
      |WHERE type IN ('PM2.5', 'SO2', 'CO','PM10','O3','NO2')
      |GROUP BY type
      |ORDER BY average ASC""".stripMargin
}
